package com.junjie.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.junjie.dao.IBookDao;
import com.junjie.entity.Book;
import com.junjie.entity.Category;
import com.junjie.util.ConfigUtil;
import com.junjie.util.DBUtil;

public class BookDaoImpl implements IBookDao{

	public List<Book> select(String sql ,Object[]parameter) throws Exception {
		List<Book> bookList = new ArrayList<Book>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		conn = DBUtil.getConnection();
		pstmt = conn.prepareStatement(sql);
		if(parameter !=null && parameter.length>0){
			for (int i = 1; i <= parameter.length; i++) {
				pstmt.setObject(i, parameter[i-1]);
			}
		}
		rs = pstmt.executeQuery();
		while(rs.next()){
			Book book = new Book();
			book.setId(rs.getInt("id"));
			book.setProductName(rs.getString("product_name"));
			book.setAuthor(rs.getString("author"));
			book.setPublishing(rs.getString("publishing"));
			book.setPublishTime(new Date(rs.getLong("publish_time")));
			book.setDescription(rs.getString("description"));
			book.setFixedPrice(rs.getDouble("fixed_price"));
			book.setDangPrice(rs.getDouble("dang_price"));
			book.setProductPic(rs.getString("product_pic"));
			bookList.add(book);
		}
		return bookList;
	}
	/**
	 * 根据category表中的id 列查询书籍
	 */
	public List<Book> findByCategoryId(Category category)throws Exception{
		return select(ConfigUtil.getValue("FIND_BOOK_BY_ID"),new Object[]{category.getId()});
	}
	/**
	 * 根据category表中的id 列
	 *  分页  查询书籍
	 */
	public List<Book> findByCategoryId(Category category,int pageNo,int pageSize)throws Exception{
		return select(ConfigUtil.getValue("FIND_BOOK_LIMIT"),new Object[]{category.getId(),pageNo,pageSize});
	}
	/**
	 * 最新添加的小说类图书,显示在主页面4条数据
	 * @throws Exception 
	 */
	public List<Book>findNewAddBook() throws Exception{
		return select(ConfigUtil.getValue("FIND_NEWADD_BOOK"),null);
	}
	/**
	 * 按条件排列查询到的bookList
	 * 按出版时间 升序
      	按出版时间 降序
          按上架时间 升序
           按上架时间 降序
	 */
	public List<Book> findByCategoryId(Category category,String str,int pageNo,int pageSize)throws Exception{
		if(str.equals("0")){
			str = "p.dang_price";
		}else if(str.equals("1")){
			str = "p.dang_price desc";
		}else if(str.equals("2")){
			str = "p.fixed_price-p.dang_price";
		}else if(str.equals("3")){
			str = "(p.fixed_price-p.dang_price) desc";
		}else if(str.equals("4")){
			str = "b.publish_time";
		}else if(str.equals("5")){
			str = "b.publish_time desc";
		}else if(str.equals("6")){
			str = "b.publishing";
		}else if(str.equals("7")){
			str = "b.publishing desc";
		}else{
			str="p.id";
		}
		return select(ConfigUtil.getValue("FIND_BOOK_BY_ID")+"order by "+str+" limit ?,?",new Object[]{category.getId(),pageNo,pageSize});
	}
	/**
	 * 根据book的id查询书信息
	 */
	public Book selectById(int id)throws Exception{
		Book book = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		conn = DBUtil.getConnection();
		pstmt = conn.prepareStatement(ConfigUtil.getValue("SELECT_BY_ID"));
		pstmt.setInt(1, id);
		rs = pstmt.executeQuery();
		if(rs.next()){
			book = new Book();
			book.setId(rs.getInt("id"));
			book.setProductName(rs.getString("product_name"));
			book.setDescription(rs.getString("description"));
			book.setAddTime(new Date(rs.getLong("add_time")));
			book.setFixedPrice(rs.getDouble("fixed_price"));
			book.setDangPrice(rs.getDouble("dang_price"));
			book.setKeywords(rs.getString("keywords"));
			book.setHasDeleted((rs.getInt("has_deleted")==0)?true:false);
			book.setProductPic(rs.getString("product_pic"));
			book.setScore(rs.getInt("score"));
			book.setAuthor(rs.getString("author"));
			book.setPublishing(rs.getString("publishing"));
			book.setPublishTime(new Date(rs.getLong("publish_time")));
			book.setWordNumber(rs.getString("word_number"));
			book.setWhichEdtion(rs.getString("which_edtion"));
			book.setTotalPage(rs.getString("total_page"));
			book.setPrintTime(new Date(rs.getLong("print_time")));
			book.setPrintNumber(rs.getString("print_number"));
			book.setIsbn(rs.getString("isbn"));
			book.setAuthorSummary(rs.getString("author_summary"));
			book.setCatalogue(rs.getString("catalogue"));
			
		}
		return book;
	}
	/**
	 * 模糊查询(根据用户输入的模糊书名,进行模糊查询)
	 * @throws Exception 
	 */
	public List<Book> findBookByLikeName(String name) throws Exception{
		return select(ConfigUtil.getValue("FIND_BOOK_BY_LIKENAME"),new Object[]{"%"+name+"%"});
	}
	/**
	 * 编辑推荐模块查询代码
	 */
	public List<Book> findBookByTuiJian()throws Exception{
		return select (ConfigUtil.getValue("TUIJIAN_BOOK"),null);
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
/**
 *测试
 * @throws Exception 
 * 
 */
	public static void main(String[] args) throws Exception {
		/*int i = 1;
		for (Book book : new BookDaoImpl().select(ConfigUtil.getValue("FIND_ALL_BOOK"),null)) {
			System.out.println("   "+(i++)+book.getProductName());
		}*/
		System.out.println(new BookDaoImpl().findBookByLikeName("人"));
	}
}
